package edu.jmu.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class UserDaoImpl implements UserDao {
	
	private JdbcTemplate jdbcTemplate;

	@Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int addUser(User user) {
		String sql = "insert into t_user(username,password) values(?,?)";
		Object[] objects = new Object[]{
			user.getUsername(),
			user.getPassword()
		};
		int flag = jdbcTemplate.update(sql,objects);
		return flag;
	}

	@Override
	public int updateUser(User user) {
		String sql = "update t_user set username=?, password = ? where userid=?";
		Object[] params = new Object[]{
			user.getUsername(),
			user.getPassword(),
			user.getUserid()
		};
		int flag = jdbcTemplate.update(sql,params);
		return flag;
	}

	@Override
	public int deleteUserById(int id) {
		String sql = "delete from t_user where userid=?";
		int flag = jdbcTemplate.update(sql,id);
		return flag;
	}

	@Override
	public User findUserById(int id) {
		String sql = "select * from t_user where userid=?";
		Object[] params = new Object[]{id};
		User user = (User)jdbcTemplate.queryForObject(sql,params,new BeanPropertyRowMapper<User>(User.class));
		//User user  = (User)jdbcTemplate.query(sql, params,new BeanPropertyRowMapper<User>(User.class));
		return user;
	}
	
	@Override
	public User findUserByIdRowMapper(int id) {
		String sql = "select * from t_user where userid=?";
		Object[] params = new Object[]{id};
		User user = (User)jdbcTemplate.queryForObject(sql,params,new UserMapper());
		return user;
	}
	
	@Override
	public User findUserByIdCallbackHandler(int id) {
		String sql = "select * from t_user where userid=?";
		Object[] params = new Object[]{id};
		
		User user = new User();
		jdbcTemplate.query(sql,params,new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
			}
		});
		return user;
	}

	@Override
	public List<User> findAllUser() {
		String sql = "select * from t_user";
		List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
		return list;
	}
	
	
	@Override
	public List<User> findAllUserCallbackHandler() {
		String sql = "select * from t_user";
		List<User> userList = new ArrayList<>();
		
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				User user = new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				userList.add(user);
			}
		});
		return userList;
	}
	
	@Override
	public int countAll(){
		String sql = "select count(*) from t_user";
		Integer rowCount  = jdbcTemplate.queryForObject(sql, Integer.class);
		return rowCount ;
	}
	
	@Override
	public void otherTest(){
		String sql = "select count(*) from t_user where username=?";
		int count = jdbcTemplate.queryForObject(sql, Integer.class,"zhang");
		System.out.printf("sql=%s,result=%d%n",sql,count);
		
		sql = "select username from t_user where userid = ?";
		String name = jdbcTemplate.queryForObject(sql, new Object[]{2}, String.class);
		System.out.printf("sql=%s,result=%s%n",sql,name);
		
		sql = "select * from t_user where userid=?";
		User user = jdbcTemplate.queryForObject(sql, 
				new Object[]{2}, 
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet rs, int rowNum) throws SQLException {
						User user = new User();
						user.setUserid(rs.getInt("userid"));
						user.setUsername(rs.getString("username"));
						user.setPassword(rs.getString("password"));
						return user;
					}
				});
		System.out.printf("sql=%s,result=%s%n",sql,user);
		
	}

	

}
